/*
 * Copyright 2017 hv0912
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.vimhe.ezimp2.handler.writer;

import com.vimhe.ezimp2.config.bean.ColumnConfig;
import com.vimhe.ezimp2.config.bean.FileConfig;
import com.vimhe.ezimp2.config.bean.RowConfig;
import com.vimhe.ezimp2.config.bean.SheetConfig;
import com.vimhe.ezimp2.exception.ImportException;
import com.vimhe.ezimp2.handler.Writer;
import com.vimhe.ezimp2.handler.bean.CellData;
import com.vimhe.ezimp2.handler.bean.FileData;
import com.vimhe.ezimp2.handler.bean.RowData;
import com.vimhe.ezimp2.handler.bean.SheetData;
import com.vimhe.ezimp2.util.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;

/**
 * 数据库数据写入器
 * <br/>通过指定的数据源，按照每个工作表为一个批次把数据批量插入到指定的数据表中
 *
 * @author Vimhe
 */
public final class DatabaseWriter implements Writer {

    private final DataSource dataSource;

    public DatabaseWriter(DataSource dataSource) {
        if (dataSource == null) {
            throw new ImportException("数据源 dataSource 不能为空");
        }

        this.dataSource = dataSource;
    }

    @Override
    public void write(CellData cellData, ColumnConfig columnConfig) {

    }

    @Override
    public void write(RowData rowData, RowConfig rowConfig) {

    }

    @Override
    public void write(SheetData sheetData, SheetConfig sheetConfig) {
        try {
            if (StringUtils.isBlank(sheetConfig.getTable())) {
                throw new ImportException("未配置表名称映射");
            }

            insertBatch(sheetConfig.getTable(), sheetData.getValue().values(), sheetConfig.getRowConfig());
        } catch (Exception e) {
            throw new ImportException("导入数据出错", e);
        }
    }

    @Override
    public void write(FileData fileData, FileConfig fileConfig) {

    }

    /**
     * 批量插入
     *
     * @param tableName   表名
     * @param rowDataList 参数集合
     * @param rowConfig   列配置
     * @throws SQLException e
     */
    private void insertBatch(String tableName, Collection<RowData> rowDataList, RowConfig rowConfig) throws SQLException {
        ColumnConfig[] columnConfigs = rowConfig.getColumnConfigMap().values().toArray(new ColumnConfig[0]);

        // 生成SQL
        String sql = generalSql(tableName, columnConfigs);

        try (Connection connection = this.dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            // 改为手动提交
            connection.setAutoCommit(false);

            for (RowData rowData : rowDataList) {
                // 找到列对应的参数
                for (int i = 0, j = columnConfigs.length; i < j; i++) {
                    String cellIndex = columnConfigs[i].getIndex();
                    CellData cellData = rowData.getValue().get(cellIndex);
                    statement.setObject(i + 1, cellData.getValue());
                }
                statement.addBatch();
            }

            statement.executeBatch();
            connection.commit();
        }
    }

    /**
     * 根据表名和字段配置创建插入sql语句
     *
     * @param tableName     表名
     * @param columnConfigs 字段配置
     * @return 创建的sql
     */
    private String generalSql(String tableName, ColumnConfig[] columnConfigs) {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO " + tableName + "(");
        StringBuilder valueBuilder = new StringBuilder(")VALUES(");

        for (ColumnConfig columnConfig : columnConfigs) {
            sqlBuilder.append(columnConfig.getColumn()).append(",");
            valueBuilder.append("?,");
        }

        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
        valueBuilder.deleteCharAt(valueBuilder.length() - 1);

        sqlBuilder.append(valueBuilder.toString());
        sqlBuilder.append(")");

        return sqlBuilder.toString();
    }

}
